package com.al.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class AddAndSelect {
	private static final String URL = "jdbc:mysql://localhost:3306/jdbcdemo?serverTimezone=UTC";
	private static final String USER = "root";
	private static final String PASSWD = "";
	
	//加载驱动，只需要加载一次
	static {
		//1.加载驱动
				try {
					Class.forName("com.mysql.cj.jdbc.Driver");
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				}
	}
	public static void add() throws ClassNotFoundException, SQLException {
		//2.建立连接
		Connection conn = DriverManager.getConnection(URL, USER, PASSWD);
		//3.获取发送执行对象，定义sql语句
		String sql = "insert into users(id, username, userpass) values(?,?,?)";
		PreparedStatement pstat = conn.prepareStatement(sql);
		//键盘输入
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入用户id：");
		int id = scanner.nextInt();
		System.out.println("请输入用户名：");
		String userName = scanner.next();
		System.out.println("请输入用户密码：");
		String userpass = scanner.next();
		//为占位符赋值
		pstat.setObject(1, id);
		pstat.setObject(2, userName);
		pstat.setObject(3, userpass);
		//4.执行sql
		int i = pstat.executeUpdate();
		//5.处理结果
		System.out.println("有"+i+"条记录被执行");
		//6.关闭资源
		pstat.close();
		conn.close();
	}
	
	public static void query() throws SQLException {
		//2.建立连接
		Connection conn = DriverManager.getConnection(URL, USER, PASSWD);
		//3.获取发送执行对象，定义sql语句
		String sql = "select * from users where id = ?";
		PreparedStatement pstat = conn.prepareStatement(sql);
		//键盘输入
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入要查询的id：");
		int id = scanner.nextInt();
		//占位符赋值
		pstat.setObject(1, id);
		//4.执行 sql
		ResultSet rs = pstat.executeQuery();
		//5.处理返回结果
		while(rs.next()) {
			System.out.print(rs.getInt(1)+"\t\t");
			System.out.print(rs.getString(2)+"\t\t");
			System.out.println(rs.getString(3));
		}
		//关闭资源
		rs.close();
		pstat.close();
		conn.close();
	}
	
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Scanner scanner = new Scanner(System.in);
		System.out.println("------选择操作-------------");
		System.out.println("1.添加数据");
		System.out.println("2.查询数据");
		System.out.println();
		int c = scanner.nextInt();
		switch(c) {
		case 1:
			add();
			break;
		case 2:
			query();
			break;
		default:
			break;
		}
	}

}
